package sql;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class Db {

    private static Db _db = null;
    
    private String dbserver, dbname, dbuser, dbpass;

    public static Db getDb() {
        if (_db == null) {
            _db = new Db();
        }
        return _db;
    }

    /**
     * Loads database connection details/credentials from db-config.properties
     */
    public Db() {
        Properties props = new Properties();
        try {
            props.load(new FileInputStream("db-config.properties"));
            dbserver = props.getProperty("dbserver");
            dbname = props.getProperty("dbname");
            dbuser = props.getProperty("dbuser");
            dbpass = props.getProperty("dbpass");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Execute a query that returns a result.
     * @param query Query to run
     */
    public void executeQuery(String query, ResultSetHandler rsh) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://" + dbserver + "/" + dbname + "?" + "user=" + dbuser + "&password=" + dbpass);

            statement = conn.createStatement();
            rs = statement.executeQuery(query);
            rsh.handleResult(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (statement != null) {
                    statement.close();
                }

                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Execute SQL that inserts/updates/deletes.
     * @param query SQL for operation
     */
    public void executeNonQuery(String query, ResultSetHandler rsh) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://" + dbserver + "/" + dbname + "?" + "user=" + dbuser + "&password=" + dbpass);
            statement = conn.createStatement();
            statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            rs = statement.getGeneratedKeys();
            rsh.handleResult(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }

                if (statement != null) {
                    statement.close();
                }

                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}
